# coding:utf-8
import time
import os
from threading import Lock, Event
import paramiko
import pymysql
import redis

r = redis.Redis(host="10.33.64.99", port=6379, db=0)
# r = redis.Redis(host="127.0.0.1", port=6379, db=0)
# conn = pymysql.connect(host="10.33.64.99", user="root", passwd="vdin1234", db="tsgz_test", charset="utf8")
conn = pymysql.connect(host="10.33.64.99", user="root", passwd="vdin1234", db="tsgz", charset="utf8")
cursor = conn.cursor()


class SysSSHClient(object):
    def __init__(self, hostname, username, password, port=22, keepalive=None):
        self.hostname = hostname
        self.username = username
        self.password = password
        self.port = port
        self.keepalive = keepalive
        self.connected = Event()
        self.lock = Lock()
        self.ssh = paramiko.SSHClient()
        self.ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())

    def _do_connect(self):
        self.ssh.connect(hostname=self.hostname, username=self.username, password=self.password, port=self.port)

    def _connect(self):
        """ Connect to the remote if not already connected. """
        if not self.connected.is_set():
            try:
                self.lock.acquire()
                # Another thread may have connected while we were
                # waiting to acquire the lock
                if not self.connected.is_set():
                    self._do_connect()
                    self.connected.set()
            except :
                raise
            finally:
                self.lock.release()

    def run_command(self, command):
        self._connect()
        stdin, stdout, stderr = self.ssh.exec_command(command)
        return stdout

    def close(self):
        self.ssh.close()

ssh = SysSSHClient(hostname='10.33.64.99', username='root', password='Vdin1234', port=22)
time_list = [ str(i)+':00:00' if i>=10 else '0'+str(i)+':00:00' for i in range(24)]
oids = ['ifOutOctets.777', 'ifInOctets.777', 'ifOutOctets.1025', 'ifInOctets.1025']
time_str = time.strftime("%Y%m%d")

n = 1
while True:
    t = time.time()
    if not time_str == time.strftime("%Y%m%d"):
        r.expire("fw_tx_" + time_str, 24 * 60 * 60)
        r.expire("fw_rx_" + time_str, 24 * 60 * 60)
        r.expire("sw_tx_" + time_str, 24 * 60 * 60)
        r.expire("sw_rx_" + time_str, 24 * 60 * 60)
        time_str = time.strftime("%Y%m%d")
    results = []
    for oid in oids:
        cmd = 'snmpwalk -v 2c -c Vdin1234 1.1.1.1 %s' % oid
        results.append(ssh.run_command(cmd).read().strip().split()[-1])
    r.hmset("fw_tx_"+time_str, {int(t): results[0]})
    r.hmset("fw_rx_" + time_str, {int(t): results[1]})
    r.hmset("sw_tx_" + time_str, {int(t): results[2]})
    r.hmset("sw_rx_" + time_str, {int(t): results[3]})

    # if time.strftime("%H:%M:%S", time.localtime(t)) not in time_list:
    if n % 60 == 0:
        print "fw_tx:", r.hget("fw_tx_"+time_str, int(t)), r.hget("fw_tx_"+time_str, int(t)-60), \
                          r.hget("fw_tx_" + time_str, int(t) - 59), r.hget("fw_tx_"+time_str, int(t)-61)
        print "fw_rx:", r.hget("fw_rx_"+time_str, int(t)), r.hget("fw_rx_" + time_str, int(t) - 60)

        delta_fw_tx = int(r.hget("fw_tx_" + time_str, int(t))) - \
                      int(r.hget("fw_tx_" + time_str, int(t) - 60) or r.hget("fw_tx_" + time_str, int(t) - 59) \
                          or r.hget("fw_tx_" + time_str, int(t) - 61) or 0)
        delta_fw_rx = int(r.hget("fw_rx_" + time_str, int(t))) - \
                      int(r.hget("fw_rx_" + time_str, int(t) - 60) or r.hget("fw_rx_" + time_str, int(t) - 59) \
                          or r.hget("fw_rx_" + time_str, int(t) - 61) or 0)
        delta_sw_tx = int(r.hget("sw_tx_" + time_str, int(t))) - \
                      int(r.hget("sw_tx_" + time_str, int(t) - 60) or r.hget("sw_tx_" + time_str, int(t) - 59) \
                          or r.hget("sw_tx_" + time_str, int(t) - 61) or 0)
        delta_sw_rx = int(r.hget("sw_rx_" + time_str, int(t))) - \
                      int(r.hget("sw_rx_" + time_str, int(t) - 60) or r.hget("sw_rx_" + time_str, int(t) - 59) \
                          or r.hget("sw_rx_" + time_str, int(t) - 61) or 0)
        print delta_fw_tx, delta_fw_rx, delta_sw_tx, delta_sw_rx
        sql = """INSERT INTO app_timeslotnetflow(firewall_tx, firewall_rx, switch_tx, switch_rx, 
              start_time, end_time) VALUES (%s, %s, %s, %s, %s, %s)"""
        try:
            cursor.execute(sql, (str(delta_fw_tx), str(delta_fw_rx), str(delta_sw_tx), str(delta_sw_rx),
                                 time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t-60)),
                                 time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t))))
            conn.commit()
        except Exception as e:
            print e
    if time.time() - t < 0.5:
        time.sleep(1)
    n += 1
